package cam.School.DataAccessObjectImpl;

import static cam.School.DataAccessObject.DAOUtil.close;
import static cam.School.DataAccessObject.DAOUtil.prepareStatement;
import static cam.School.DataAccessObject.DAOUtil.toSqlDate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cam.School.DAOException.DAOException;
import cam.School.DataAccessObject.DAOFactory;
import cam.School.DataAccessObject.UserRequestDAO;
import cam.School.DataTransfertObject.CallResult;
import cam.School.DataTransfertObject.EventDTO;
import cam.School.DataTransfertObject.UserRequestDTO;


public class UserRequestDAOImpl implements UserRequestDAO  {

// Constants ----------------------------------------------------------------------------------
		
    private static final String QUERY_PARAMS_GET_ALL ="id, origin, destination, date,"+
                         " status, description, responseSeen, result, comment";
        
    private static final String UPDATE_QUERY_PARAMS = " origin = ?, destination = ?, date = ?,"+
            " status = ?, description = ?, responseSeen = ?, result = ?, comment = ?";
    
    
    private static final String SQL_FIND_BY_ORIGIN = "SELECT "+ 
    		QUERY_PARAMS_GET_ALL + " FROM userrequest WHERE  origin = ?";
    
    private static final String INSERT_PARAMS = "(description, origin, destination, status, date, responseSeen, result, comment)";
    
    private static final String SQL_INSERT =
            "INSERT INTO userrequest "+INSERT_PARAMS+" VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
    
   
    private DAOFactory daoFactory;
    
 // Constructors -------------------------------------------------------------------------------

    /**
     * Construct an User DAO for the given DAOFactory. Package private so that it can be constructed
     * inside the DAO package only.
     * @param daoFactory The DAOFactory to construct this User DAO for.
     */
    public UserRequestDAOImpl(DAOFactory daoFactory) {
        this.daoFactory = daoFactory;
    }
	@Override
	public UserRequestDTO getById(Long id) throws DAOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<UserRequestDTO> getAll() throws DAOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallResult create(UserRequestDTO request){
		Object[] values = {
				request.getDescription(),
				request.getOrigin(), 
				request.getDestination(), 
				request.getStatus(),
				toSqlDate(new Date()),	
				false,
				"non decider",
				""
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_INSERT, false,
					values);
			int affectedRows = preparedStatement.executeUpdate();			
			if (affectedRows == 0) {
				return new CallResult(404, "not created");
			} else {
				return new CallResult(200, "created");
			}
		} catch (SQLException e) {
			return new CallResult(404, e.toString());
		} finally {
			close(connection, preparedStatement);
		}
	}

	@Override
	public boolean delete(int requestId) {
		// TODO Auto-generated method stub
		return false;
	}
	

	@Override
	public List<UserRequestDTO> getByOrigin(long from) {
		Object[] values = {from};
		return getAll(SQL_FIND_BY_ORIGIN, values);			
	}

	private UserRequestDTO map(ResultSet resultSet)
			throws SQLException {
		UserRequestDTO request = new UserRequestDTO();
		request.setId(resultSet.getLong("id"));
		request.setOrigin(resultSet.getLong("origin"));
		request.setDestination(resultSet.getLong("destination"));
		request.setDate(resultSet.getDate("date"));
		request.setDescription(resultSet.getString("description"));
		request.setStatus(resultSet.getString("status"));	
		request.setResponseSeen(resultSet.getBoolean("responseSeen"));
		request.setResult(resultSet.getString("result"));
		request.setComment(resultSet.getString("comment"));
		return request;
	}
	
	public List<UserRequestDTO> getAll(String sqlQuery, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<UserRequestDTO> requests = new ArrayList<UserRequestDTO>();

		try {
			connection = daoFactory.getConnection();
			if(values.length == 0 ){
				preparedStatement = connection.prepareStatement(sqlQuery);
			}else{
				preparedStatement = prepareStatement(connection, sqlQuery, false, values);
			}
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				requests.add(map(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return requests;
	}

	@Override
	public List<UserRequestDTO> getByDate(Date date) {
		// TODO Auto-generated method stub
		return null;
	}
	
	
	@Override
	public boolean update(UserRequestDTO request) {
		// TODO Auto-generated method stub
		return true;
	}
	
	@Override
	public UserRequestDTO getByDescription(String description) {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public UserRequestDTO getByDestination(long id) {
		// TODO Auto-generated method stub
		return null;
	}
}
